package tw.fatsung.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

import javax.naming.NamingException;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DbManager {
	
	private static final Logger log=LoggerFactory.getLogger(DbManager.class);
	private final static String lokup="java:comp/env/jdbc/db1";
	private static DataSource ds;
	
	/**
	 *<B style='color:red;'>查詢資料</B>
	 *@param String sql指令敍述 
	 *@param Object 傳進來的值,多值
	 *@return ResultSet查詢的資料
	
	public static ResultSet executeQuery(String sql, Object...params )throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			setParams(pstmt,params);
			return pstmt.executeQuery();
		}finally{
			if(pstmt!=null) pstmt.close();
			if(conn != null) conn.close();
		}
	}
	*/
	
	/**
	 *<B style='color:red;'>執行異動資料</B>
	 *@param String sql指令敍述 
	 *@param Object 傳進來的值,多值
	 *@return 執行SQL,傳回影響行數
	*/
	public static int executeUpdate(String sql, Object...params )throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			setParams(pstmt,params);
		return pstmt.executeUpdate();
		}finally{
			close(pstmt);
			close(conn);
		}
	}

	/**
	 *<B style='color:red;'>處理PreparedStatement的設定</B>
	 *@param PreparedStatement  
	 *@param Object 傳進來的值,多值
	 *@return 無
	*/
	private static void setParams(PreparedStatement pstmt, Object...params) throws SQLException{
		if(params == null || params.length ==0){
			return;
		}
		for(int i=1; i<=params.length; i++){
			Object param=params[i-1];
			if(param==null){
				pstmt.setNull(i,Types.NULL);
			}else if(param instanceof Integer){
				pstmt.setInt(i,(Integer)param); //設定Integer參數
			}else if(param instanceof String){
				pstmt.setString(i,(String)param);//設定String參數
			}else if(param instanceof Double){
				pstmt.setDouble(i,(Double)param);//設定Double參數
			}else if(param instanceof Long){
				pstmt.setLong(i,(Long)param);	//設定Long參數
			}else if(param instanceof Timestamp){
				pstmt.setTimestamp(i,(Timestamp)param);//設定Timestamp參數
			}else if(param instanceof Boolean){
				pstmt.setBoolean(i,(Boolean)param);//設定Boolean參數
			}else if(param instanceof Date){
				pstmt.setDate(i,(Date)param);	//設定Date參數
			}
		}
	}

	/**
	 *<B style='color:red;'>讀取筆數</B>
	 *@param String sql指令敍述 
	 *@param Object多個參數集合
	 *@return 查詢的筆數
	*/
	public static int getCount(String sql,Object...params)throws SQLException{
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=getConnection();
			pstmt=conn.prepareStatement(sql);
			setParams(pstmt,params);
			rs=pstmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
	}
	
	/**
	 *<B style='color:red;'>讀取筆數</B>
	 *@param String sql指令敍述 
	 *@return 查詢的筆數
	*/
	public static int getCount(String sql)throws SQLException{
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try{
			conn=getConnection();
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			rs.next();
			return rs.getInt(1);
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
	}
	/**
	 *<B style='color:red;'>取得資料庫連線</B>
	 *@param 無
	 *@return Connection 資料庫連線
	*/
	public static Connection getConnection() throws SQLException{
		if(ds==null){
			loadDs();
		}
		return ds.getConnection();
	}
	
	/**
	 *<B style='color:red;'>建立{@code javax.sql.DataSource}</B>
	 * @throws SQLException 
	*/
	private static void loadDs() throws SQLException{
		javax.naming.InitialContext ctx = null;
		try {
			ctx	= new javax.naming.InitialContext();
			ds	= (javax.sql.DataSource) ctx.lookup(lokup);
		} catch (NamingException e) {
			log.error("建立DataSource失敗!",e);
			throw new SQLException(e);
		}finally{
			try {
				if(ctx!=null)
					ctx.close();
			} catch (NamingException e) {
				log.warn("Close InitialContext error ",e);
			}
			ctx=null;
		}
	}
	
	public static void close(ResultSet rs){
		if(rs!=null){
			try{
				rs.close();
			}catch (Exception e) {
				log.warn("Close ResultSet error ",e);
			}
		}
	}
	
	public static void close(Statement st){
		if(st!=null){
			try{
				st.close();
			}catch (Exception e) {
				log.warn("Close Statement error ",e);
			}
		}
	}
	
	public static void close(Connection conn){
		if(conn!=null){
			try{
				conn.close();
			}catch (Exception e) {
				log.warn("Close Connection error ",e);
			}
		}
	}
}
